package com.haiyou.data.common.persister.mysql.help;

import java.io.Serializable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.lang3.Validate;

import com.haiyou.common.entity.Entity;
import com.haiyou.common.util.string.StringUtils;
import com.haiyou.data.common.persister.mysql.meta.ColumnMeta;
import com.haiyou.data.common.persister.mysql.meta.IdColumnMeta;
import com.haiyou.data.common.persister.mysql.meta.IndexMeta;
import com.haiyou.data.common.persister.mysql.meta.TableMeta;

import com.haiyou.data.common.persister.mysql.annotation.Table.RollPolicy;
import com.haiyou.data.common.persister.mysql.annotation.Index.IndexPolicy;

public class SqlHelper {

	public static String createTableIfNotExists(TableMeta tableMeta, String db, String tab) {
		StringBuilder builder = new StringBuilder();
		builder.append(String.format("create table if not exists `%s`.`%s`(\r\n", db, tab));
		IdColumnMeta idColumnMeta = tableMeta.getIdColumnMeta();
		for (int i = 0; i < tableMeta.getColumnMetas().size(); i++) {
			ColumnMeta columnMeta = tableMeta.getColumnMetas().get(i);

			if (i == 0 && idColumnMeta != null) {// 主键
				Validate.isTrue(columnMeta == idColumnMeta);
				builder.append("\t`").append(idColumnMeta.getColumnName()).append("` ")
						.append(idColumnMeta.getColumnType());
				builder.append(" not null ");// 主键不允许为空
				java.lang.reflect.Type type = ColumnHelper.getGenericType(columnMeta);
				if (idColumnMeta.isAutoIncr() && (type == Long.class || type == Integer.class)) {
					builder.append(" auto_increment ");// 只有整形或者长整型做key才允许设置自增ID
				}
			} else {// 成员字段
				builder.append("\t`").append(columnMeta.getColumnName()).append("` ")
						.append(columnMeta.getColumnType());
				if (!columnMeta.isCanBeNull()) {
					builder.append(" not null ");
				}
			}

			if (!StringUtils.isEmpty(columnMeta.getComment())) {// 描述
				String comment = String.format("comment '%s' ", columnMeta.getComment());
				builder.append(" ").append(comment).append(" ");
			}

			if (i < tableMeta.getColumnMetas().size() - 1) {
				builder.append(",\r\n");
			}

			if (i == tableMeta.getColumnMetas().size() - 1) {// 最后一个字段
				if (idColumnMeta != null) {// 设置主键
					builder.append(",\r\n");
					builder.append(String.format("\t primary key (`%s`)", idColumnMeta.getColumnName()));
				}

				if (tableMeta.getIndexMetas() != null && tableMeta.getIndexMetas().length > 0) {
					builder.append(",\r\n");
					for (int k = 0; k < tableMeta.getIndexMetas().length; k++) {
						IndexMeta indexMeta = tableMeta.getIndexMetas()[k];
						if (indexMeta.getPolicy() != IndexPolicy.FULLTEXT) {
							builder.append(String.format("\t%s key `%s` (%s) using %s",
									indexMeta.getPolicy() == IndexPolicy.NORMAL ? "" : indexMeta.getPolicy().type,
									indexMeta.getName(), IndexPolicy.multiColumn(indexMeta.getColumns()),
									indexMeta.getPolicy().way));
						} else {
							builder.append(String.format("\t%s key `%s` (%s)", indexMeta.getPolicy().type,
									indexMeta.getName(), IndexPolicy.multiColumn(indexMeta.getColumns())));
						}
						if (k < tableMeta.getIndexMetas().length - 1) {
							builder.append(",\r\n");
						} else {
							builder.append("\r\n");
						}
					}
				} else {
					builder.append("\r\n");
				}
			}
		}

		builder.append(")");

		if (idColumnMeta != null && idColumnMeta.isAutoIncr() && idColumnMeta.getBeginValue() > 0) {
			builder.append(String.format("auto_increment=%d,", idColumnMeta.getBeginValue()));// 设置自增属性
		}

		builder.append(String.format("engine=%s default charset=%s collate %s comment='%s'", tableMeta.getEngine(),
				tableMeta.getCharset(), tableMeta.getCollate(), tableMeta.getComment()));// 设置编码,排序规则,描述

		// TODO 如果表中存在text，blob等动态数据就动态表，否则就是静态表，通常不做设置
		// ROW_FORMAT=DYNAMIC

		return builder.toString();
	}

	private static String buildColumns(TableMeta tableMeta) {
		StringBuilder builder = new StringBuilder();
		for (int i = 0; i < tableMeta.getColumnMetas().size(); i++) {
			ColumnMeta columnMeta = tableMeta.getColumnMetas().get(i);
			builder.append("`").append(columnMeta.getColumnName()).append("`");
			if (i < tableMeta.getColumnMetas().size() - 1) {
				builder.append(",");
			} else {
				builder.append(" ");
			}
		}
		return builder.toString();
	}

	public static String query(Class<? extends Entity<?>> entityClass, String db, String tab, Serializable id) {
		TableMeta tableMeta = TableHelper.getTableMeta(entityClass);
		Objects.requireNonNull(tableMeta.getIdColumnMeta());
		StringBuilder builder = new StringBuilder();
		builder.append("select " + buildColumns(tableMeta));
		builder.append(String.format("from `%s`.`%s` where `%s`='%s'", db, tab, TableHelper.getPkName(tableMeta), id));
		return builder.toString();
	}

	public static String queryBatch(Class<? extends Entity<?>> entityClass, String db, String tab,
			Iterable<? extends Serializable> ids) {
		TableMeta tableMeta = TableHelper.getTableMeta(entityClass);
		Objects.requireNonNull(tableMeta.getIdColumnMeta());
		StringBuilder builder = new StringBuilder();
		builder.append("select " + buildColumns(tableMeta));
		String condition = "from `%s`.`%s` where `%s` in (%s)";
		StringBuilder sb = new StringBuilder();
		Iterator<?> iterator = ids.iterator();
		while (iterator.hasNext()) {
			Serializable id = (Serializable) iterator.next();
			sb.append("'").append(id).append("'").append(",");
		}
		String s = sb.toString();
		builder.append(
				String.format(condition, db, tab, TableHelper.getPkName(tableMeta), s.substring(0, s.length() - 1)));
		return builder.toString();
	}

	public static String insert(Entity<?> entity, String db, String tab, boolean includePk) {
		Class<? extends Entity<?>> entityClass = (Class<? extends Entity<?>>) entity.getClass();
		TableMeta tableMeta = TableHelper.getTableMeta(entityClass);
		StringBuilder builder = new StringBuilder();
		builder.append(String.format("insert into `%s`.`%s` (\r\n", db, tab));
		int skip = includePk ? 0 : 1;

		List<Object> vlist = Lists.newArrayList();
		for (int i = skip; i < tableMeta.getColumnMetas().size(); i++) {
			ColumnMeta columnMeta = tableMeta.getColumnMetas().get(i);
			Object x = ColumnHelper.cast(columnMeta, entity);
			if(Objects.nonNull(x)){
				builder.append("`").append(columnMeta.getColumnName()).append("`");
				if (i < tableMeta.getColumnMetas().size() - 1) {
					builder.append(",");
				} else {
					builder.append(")\r\nvalues (\r\n");
				}
				vlist.add(x);
			}
		}

		for (int i = 0; i < vlist.size(); i++) {
			builder.append("'").append(vlist.get(i)).append("'");
			if (i < vlist.size() - 1) {
				builder.append(",");
			} else {
				builder.append(")");
			}
		}

//		for (int i = skip; i < tableMeta.getColumnMetas().size(); i++) {
//			ColumnMeta columnMeta = tableMeta.getColumnMetas().get(i);
//			builder.append("`").append(columnMeta.getColumnName()).append("`");
//			if (i < tableMeta.getColumnMetas().size() - 1) {
//				builder.append(",");
//			} else {
//				builder.append(")\r\nvalues (\r\n");
//			}
//		}
//		for (int i = skip; i < tableMeta.getColumnMetas().size(); i++) {
//			ColumnMeta columnMeta = tableMeta.getColumnMetas().get(i);
//			Object x = ColumnHelper.cast(columnMeta, entity);
//			builder.append("'").append(x).append("'");
//			if (i < tableMeta.getColumnMetas().size() - 1) {
//				builder.append(",");
//			} else {
//				builder.append(")");
//			}
//		}
		return builder.toString();
	}

	public static String replace(Entity<?> entity, String db, String tab) {
		return insert(entity, db, tab, true).replace("insert", "replace");
	}

	public static String insertIfNotExists(Entity<?> entity, String db, String tab) {
		return insert(entity, db, tab, true).replace("insert", "insert ignore");
	}

	private static void update(Entity<?> entity, TableMeta tableMeta, int index, StringBuilder builder) {
		for (int i = index; i < tableMeta.getColumnMetas().size(); i++) {
			ColumnMeta columnMeta = tableMeta.getColumnMetas().get(i);
			Object x = ColumnHelper.cast(columnMeta, entity);
			if(Objects.nonNull(x)){
				builder.append("`").append(columnMeta.getColumnName()).append("`='").append(x).append("'");
				if (i < tableMeta.getColumnMetas().size() - 1) {
					builder.append(",");
				}
			}
		}
	}

	private static void updateExcludeAutoId(Entity<?> entity, TableMeta tableMeta, int index, StringBuilder builder) {
		for (int i = index; i < tableMeta.getColumnMetas().size(); i++) {
			ColumnMeta columnMeta = tableMeta.getColumnMetas().get(i);
            if(columnMeta instanceof IdColumnMeta){
            	IdColumnMeta meta = (IdColumnMeta) columnMeta;
                if(meta.isAutoIncr()){
                    continue;
                }
            }
			builder.append("`").append(columnMeta.getColumnName()).append("`='");
			Object x = ColumnHelper.cast(columnMeta, entity);
			builder.append(x).append("'");
			if (i < tableMeta.getColumnMetas().size() - 1) {
				builder.append(",");
			}
		}
	}

	public static String insertOnDuplicateKeyUpdate(Entity<?> entity, String db, String tab) {
		StringBuilder builder = new StringBuilder();
		builder.append(insert(entity, db, tab, true));
		builder.append("\r\non duplicate key update\r\n");
		TableMeta tableMeta = TableHelper.getTableMeta((Class<? extends Entity<?>>) entity.getClass());
		update(entity, tableMeta, 0, builder);
		return builder.toString();
	}

	public static String insertOnDuplicateKeyUpdateExcludeAutoId(Entity<?> entity, String db, String tab) {
		StringBuilder builder = new StringBuilder();
		builder.append(insert(entity, db, tab, false));
		builder.append("\r\non duplicate key update\r\n");
		TableMeta tableMeta = TableHelper.getTableMeta((Class<? extends Entity<?>>) entity.getClass());
        updateExcludeAutoId(entity, tableMeta, 0, builder);
		return builder.toString();
	}

	public static String update(Entity<?> entity, String db, String tab) {
		Class<? extends Entity<?>> entityClass = (Class<? extends Entity<?>>) entity.getClass();
		TableMeta tableMeta = TableHelper.getTableMeta(entityClass);
		Objects.requireNonNull(tableMeta.getIdColumnMeta());
		StringBuilder builder = new StringBuilder();
		builder.append(String.format("update `%s`.`%s` set ", db, tab));
		update(entity, tableMeta, 1, builder);
		builder.append(String.format(" where `%s`='%s'", TableHelper.getPkName(tableMeta), entity.getId()));
		return builder.toString();
	}

	public static String delete(String db, String tab, String pkName, Iterable<? extends Serializable> ids) {
		StringBuilder sb = new StringBuilder();
		Iterator<?> iterator = ids.iterator();
		while (iterator.hasNext()) {
			Serializable id = (Serializable) iterator.next();
			sb.append("'").append(id).append("'").append(',');
		}
		String s = sb.toString();
		return String.format("delete from `%s`.`%s` where `%s` in (%s)", db, tab, pkName,
				s.substring(0, s.length() - 1));
	}

	public static String preparedInsertIfNotExists(Class<? extends Entity<?>> entityClass, String db, String tab) {
		return preparedInsert(entityClass, db, tab, true).replace("insert", "insert ignore");
	}

	public static String preparedReplace(Class<? extends Entity<?>> entityClass, String db, String tab) {
		return preparedInsert(entityClass, db, tab, true).replace("insert", "replace");
	}

	public static String preparedInsert(Class<? extends Entity<?>> entityClass, String db, String tab,
			boolean includePk) {
		TableMeta tableMeta = TableHelper.getTableMeta(entityClass);
		StringBuilder builder = new StringBuilder();
		builder.append(String.format("insert into `%s`.`%s` (\r\n", db, tab));
		int skip = includePk ? 0 : 1;
		for (int i = skip; i < tableMeta.getColumnMetas().size(); i++) {
			ColumnMeta columnMeta = tableMeta.getColumnMetas().get(i);
			builder.append("`").append(columnMeta.getColumnName()).append("`");
			if (i < tableMeta.getColumnMetas().size() - 1) {
				builder.append(",");
			} else {
				builder.append(")\r\nvalues (\r\n");
			}
		}
		for (int i = skip; i < tableMeta.getColumnMetas().size(); i++) {
			builder.append('?');
			if (i < tableMeta.getColumnMetas().size() - 1) {
				builder.append(",");
			} else {
				builder.append(")");
			}
		}
		return builder.toString();
	}

	private static void preparedUpdate(TableMeta tableMeta, int index, StringBuilder builder) {
		for (int i = index; i < tableMeta.getColumnMetas().size(); i++) {
			ColumnMeta columnMeta = tableMeta.getColumnMetas().get(i);
			builder.append("`").append(columnMeta.getColumnName()).append("`=");
			builder.append('?');
			if (i < tableMeta.getColumnMetas().size() - 1) {
				builder.append(",");
			}
		}
	}

	public static String preparedInsertOnDuplicateKeyUpdate(Class<? extends Entity<?>> entityClass, String db,
			String tab) {
		TableMeta tableMeta = TableHelper.getTableMeta(entityClass);
		StringBuilder builder = new StringBuilder();
		builder.append(preparedInsert(entityClass, db, tab, true));
		builder.append("\r\non duplicate key update\r\n");
		preparedUpdate(tableMeta, 0, builder);
		return builder.toString();
	}

	public static String preparedUpdate(Class<? extends Entity<?>> entityClass, String db, String tab) {
		TableMeta tableMeta = TableHelper.getTableMeta(entityClass);
		Objects.requireNonNull(tableMeta.getIdColumnMeta());
		StringBuilder builder = new StringBuilder();
		builder.append(String.format("update `%s`.`%s` set ", db, tab));
		preparedUpdate(tableMeta, 1, builder);
		builder.append(String.format(" where `%s`=?", TableHelper.getPkName(tableMeta)));
		return builder.toString();
	}
}
